Outside Data Correlation

I.  New York Times

We're going to be using the New York Times search API in this lab, so I suggest everyone familiarize themselves with their developers site: 

http://developer.nytimes.com/docs/read/article_search_api

Times has a huge amount of useful resources for accessing their database/content.  It's really worth checking out what they offer.  For instance, we're basically just going to be working with the "article search api"  and the "best seller list api" but they offer about 12 others, from geographically categorized topics, to campaign finance and congressional voting records.  They even have tools to access comments that people post throughout the ny times blog.  They've basically provided well documented and efficient tools to access most of their data and since they're arguably the largest news source on the planet, that's quite a resource.  

You'll need to sign up for an API key through their site: 

http://developer.nytimes.com/

Click on "request an API key" and follow the instructions.  When you finish you'll get a code that looks like this: 

Article Search API key: 	2c4094f9012ef6f5c40ba2c6f74c9d78:7:65423204

I've included my own key in the processing sketch that we're using for this lab but that's limited to like 5000 queries per day, so it'll work for class but we can't all use it forever.  

-----
The Processing examples we're using today are taken from a blog has instructions on how to (efficiently) use the NY Times API in processing.  If you're interested in the site, here are links: 

Simple example:
http://blog.blprnt.com/blog/blprnt/processing-json-the-new-york-times

Advanced example:
http://blog.blprnt.com/blog/blprnt/multi-faceted-searching-with-the-nytimes-apis

-----
We can also access best seller information for books ( !! ) using their best seller API.  Here's an example search:

http://api.nytimes.com/svc/books/v2/lists.json?list=hardcover-fiction&weeks-on-list=2&api-key=6cdee3154c060122eee6adac15be577e:17:65423204

*You'll have to use a separate best seller api key, so remember to request one when you're signing up.  Here's mine again just in case: 

Best seller list: 	6cdee3154c060122eee6adac15be577e:17:65423204
Article search: 	2c4094f9012ef6f5c40ba2c6f74c9d78:7:65423204

II. Google Correlate 

Export data from Mysql and correlate with Google Correlate Instructions: 

1. Run query.  You have to make sure that your query only returns two columns.  The first should be date, the second should be some kind of count.  Google correlate only graphs frequency over time, so we're not doing anything more complicated than this.  It's also best if you format the date like this "2012-01-01" so look at these example queries:

select DATE_FORMAT(cout, '%Y-%m-%d'), count(*) from inraw where title like '%vampire%' and cout > '2006-01-01' and cout < '2012-01-01' group by year(cout), month(cout) order by year(cout), month(cout);

2. Save the data either in Sequel Pro or in Processing like we've been doing. 
	a) in Sequel Pro click file-> export-> custom query results-> csv file
	b) in Processing, comment this like out, so that the first row doesn't have the SQL query: 
		//textArray[0] = TheQuery;
	c) In HeidiSQL, right click the results and click "Export grid data to file"
3. Save as a csv file. 
4. Important: you will need to go into a text editor or Excel and make sure that the first row of your exported data is a date and value. It must NOT be titles for the columns or an SQL function. Google Correlate freaks out if it's anything but data in the first row.  
5. Go to: http://www.google.com/trends/correlate/ and select "Enter your own data" next to the search field
6. Click "choose file" and select your file, give it a moment to load.
7. Select either weekly or monthly time series and enter a name.
8. Hit Search.
9.  Learn more about what this data means: http://www.google.com/trends/correlate/tutorial/

More examples:
SELECT DATE_FORMAT(cout, '%Y-%m-%d') as cout, count(barcode) FROM inraw WHERE (title LIKE '%Toy Story%' OR subj LIKE '%Toy Story%' ) AND YEAR(cout) > '2005' GROUP BY DATE_FORMAT(cout, '%Y-%m-%d');

SELECT DATE_FORMAT(cout, '%Y-%m-%d') AS checkoutDate, count(barcode) FROM inraw WHERE (title LIKE '%olympics%' OR subj LIKE '%olympics%') AND YEAR(cout) > '2005' GROUP BY DATE_FORMAT(cout, '%Y-%m-%d');

https://docs.google.com/spreadsheet/ccc?key=0AiOWmapLqW9hdE5NWWY0cE5lTWQ5VDljaWRab1ZyblE&hl=en_US#gid=1

III. Other data resources

1.  Any of the other 12 NYTimes APIs that we haven't looked at
	The Campaign Finance API
	The Community API
	The Congress API
	The Districts API
	The Event Listings API
	The Most Popular API
	The Movie Reviews API
	The NY State Legislature API
	The Real Estate API
	The Times Newswire API
	The TimesPeople API
	The TimesTags API

2.  The Seattle Public Library's actual website
They allow you to do all sorts of advanced queries to find books.  For instance, this is one that will show all books that have been acquired by the central branch (the one we're looking at) over the past 30 days:

http://seattle.bibliocommons.com/search?custom_edit=false&custom_query=formatcode%3A%22BK%22+branch%3A%22Central+Library%22&sort%5Bdirection%5D=descending&sort%5Bfield%5D=NEWLY_ACQUIRED&sort%5Btype%5D=BIB_FIELDS&suppress=true&f_newly_acquired=PAST_30_DAYS

3. Google Hot Trends 
shows the quickest rising search terms both historically and in real time
http://www.google.com/trends/hottrends

4. Amazon
Their developer API is very poorly documented and difficult to use, however you might be able to grab some useful information there
https://affiliate-program.amazon.com/gp/advertising/api/detail/main.html

5.  Google Images or Flicker
Both of these are relatively easy to use.  Flicker has the most open API.  Google Images limits you to something horribly small like 30 images a day before you have to start paying money to use their service.  It's easy to find a way around this by creating your own query through java. 

6.  Manual correlation
Many visualizations will include manually gathered events or culturally relevant issues that are related to the data being visualized.  For instance, you might show the popularity of the term "tsunami" over the past 2 years and include markers on February 22 and March 11, 2011 when New Zeland and Japan were hit.  Etc. 

7.  Other source????  
Basically anything you can access through a browser you can correlate in one of three ways:
	1.  algorithmically if the website allows querying like the New York Times or Google Correlate
	2.  html parsing in java or another language ("rolling your own" API) 
	3.  manually, by copy/pasting the information and putting it into Processing